package org.lizar.util;

import java.io.File;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ExcelUtils {

	/**
	 * 
	 * @param InputStream is excel输入流
	 * @param int startRow 从第几行开始读取，0表示第一行
	 * 
	 * */
	public static List<List<String>> readExcel(InputStream is,int startRow) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		List<List<String>> input = new LinkedList<List<String>>();
		Workbook resources=null;
		try {
			resources = Workbook.getWorkbook(is);
			Sheet table = resources.getSheet(0);
			int rs = table.getColumns();
			int rows = table.getRows();
			for (int k = 0; k < rows; k++) {// 行
				if (k >= startRow) {
					List<String> new_list = new LinkedList<String>();
					for (int i = 0; i < rs; i++) {// 列
						Cell c00 = table.getCell(i, k);
						String strc00 = c00.getContents();
						if (c00.getType() == CellType.LABEL) {
							LabelCell labelc00 = (LabelCell) c00;
							strc00 = labelc00.getString();
						}
						if (c00.getType() == CellType.DATE) {
							DateCell dc = (DateCell) c00;
							strc00 = sdf.format(dc.getDate());
						}
						new_list.add(strc00);
					}
					input.add(new_list);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(resources!=null){
				resources.close();
			}
		}
		return input;
	}
	
	/**
	 * 输出Excel
	 * 
	 * @param os
	 */
	public static void writeExcel(String sheetName,List<List<String>> data,OutputStream os) {
		try {
			WritableWorkbook wwb = Workbook.createWorkbook(os);
			
			WritableSheet ws = wwb.createSheet(sheetName, 0);
			int i=0;
			for(List<String> row:data){
				int j=0;
				for(String cell:row){
					ws.addCell(new Label(j, i, cell));
					j++;
				}
				i++;
			}
			wwb.write();
			wwb.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void writeExcel(String sheetName,List<List<String>> data,File file) {
		if(file.exists()){
			file.delete();
		}
		try {
			WritableWorkbook wwb = Workbook.createWorkbook(file);
			
			WritableSheet ws = wwb.createSheet(sheetName, 0);
			int i=0;
			for(List<String> row:data){
				int j=0;
				for(String cell:row){
					ws.addCell(new Label(j, i, cell));
					j++;
				}
				i++;
			}
			wwb.write();
			wwb.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
